package com.Cfeng.XiaohuanChat.repository;

import com.Cfeng.XiaohuanChat.entity.ChatUser;
import com.Cfeng.XiaohuanChat.entity.UserFile;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
 * @author Cfeng
 * @date 2022/8/12
 */

public interface UserFileRepository extends JpaRepository<UserFile,Long> {

    //查找文件
    UserFile findByFileNameAndUser(String fileName, ChatUser user);

    //按照url查询
    UserFile findByUrl(String url);

    //根据文件的父Id查询文件列表,多字段排序，每个字段都需要加上SORT, 按照文件夹/文件 和文件上传时间排序
    List<UserFile> findAllByParentIdOrderByIsDirDescUploadTimeDesc(Long parentId);

    //根据文件的isDir查询文件列表，多字段排序
    List<UserFile> findAllByIsDirOrderByIsDirDescUploadTimeDesc(Boolean isDir);

    //查询当前文件夹下面所有的非文件夹的指定名称的文件的数量
    Integer countByFileNameAndIsDirAndParentId(String fileName,boolean isDir, Long parentId);

    //查询所有符合条件的parentId的序列
    List<UserFile> findAllByParentId(Long parentId);

    //查询所有的父结点
    @Query(value = "SELECT * FROM ( SELECT @r AS _id,( SELECT @r := parent_id FROM user_file WHERE id = _id ) AS parent_id,@l := @l + 1 AS lvl FROM ( SELECT @r := #{id} id, @l := 0 ) vars,user_file h WHERE @r <![CDATA[ <> ]]> 0) T1 JOIN file_info T2 ON T1._id = T2.id ORDER BY T1.lvl DESC", nativeQuery = true)
    List<UserFile> selectParentList(Long id);
}
